﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class DisbursementDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.DisbursementTableAdapter tda;
        private SSISDataSetTableAdapters.DisbursementDetailsTableAdapter detailstda;
        private SSISDataSetTableAdapters.UnCollectedTimeTableAdapter unCollectedtda;
        SSISDataSetTableAdapters.DisbursementAndCollectionTableAdapter dc_tda;

        int index;
        private List<Disbursement> disb_list;
        private List<DisbursementDetails> disbDetails_list;
        private Disbursement disb;
        private DisbursementDetails disbDetails;
        private SSISDataSet.DisbursementDataTable disburs_td;
        private SSISDataSet.DisbursementDetailsDataTable disbursDetails_td;
        private SSISDataSet.UnCollectedTimeDataTable unCollected_td;
        private SSISDataSet.DisbursementCatalogueDataTable dc_td;

         //singleton
        private static bool instance_flag = false;
        private static DisbursementDBBroker instance;


        private DisbursementDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.DisbursementTableAdapter();
            detailstda = new SSISDataSetTableAdapters.DisbursementDetailsTableAdapter();
            unCollectedtda = new SSISDataSetTableAdapters.UnCollectedTimeTableAdapter();
            dc_tda = new SSISDataSetTableAdapters.DisbursementAndCollectionTableAdapter();

            //tda.Fill(ds.Disbursement);
            //detailstda.Fill(ds.DisbursementDetails);
            //unCollectedtda.FillUnCollectedTimeByDept(ds.UnCollectedTime);
        }

        public static DisbursementDBBroker getDisbursementDBBroker()
        {
            if (!instance_flag)
            {
                instance = new DisbursementDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Disbursement> selectAllDisbursement()
        {
            tda.Fill(ds.Disbursement);
            disb_list = new List<Disbursement>();
            for (int i = 0; i < ds.Disbursement.Rows.Count; i++)
            {
                disb = new Disbursement();
                disb.DisbursementId = ds.Disbursement[i].DisbursementId;
                disb.DeptId = ds.Disbursement[i].DeptId;
                disb.DisburseDate = ds.Disbursement[i].DisburseDate;
                disb.Status = ds.Disbursement[i].Status;
                disb.CollectionId = ds.Disbursement[i].CollectionId;
                disb.RepresentativeId = ds.Disbursement[i].RepresentativeId;

                //Detail
                disbDetails_list = selectDsibursementDetailsbyDisbursId(ds.Disbursement[i].DisbursementId);
                disb.DisburDetails_list = disbDetails_list;

                disb_list.Add(disb);
            }
            return disb_list;
        }


        public int addDisbursement(Disbursement disburs)
        {
            SSISDataSet.DisbursementRow newRow = ds.Disbursement.NewDisbursementRow();
            newRow.DisbursementId = disburs.DisbursementId;
            newRow.DeptId = disburs.DeptId;
            newRow.DisburseDate = disburs.DisburseDate;
            newRow.Status = disburs.Status;
            newRow.CollectionId = disburs.CollectionId;
            newRow.RepresentativeId = disburs.RepresentativeId;
                     
            ds.Disbursement.Rows.Add(newRow);
            return tda.Update(ds.Disbursement);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.DisbursementRow row = ds.Disbursement.FindByDisbursementId(Id);
        //    return ds.Disbursement.Rows.IndexOf(row);
        //}

        private SSISDataSet.DisbursementRow getRowById(int Id)
        {
            tda.Fill(ds.Disbursement);
            return ds.Disbursement.FindByDisbursementId(Id);
        }

        public int updateDisbursement(Disbursement disburs, int Id)
        {
            SSISDataSet.DisbursementRow row = getRowById(Id);
            if (row != null)
            {
                if (disburs.Status != null && !(disburs.Status.Equals("")))
                {
                    row.Status = disburs.Status;
                }
                if(disburs.DeptId != null && !(disburs.DeptId.Equals("")))
                {
                    row.DeptId = disburs.DeptId;
                }
                if(disburs.DisburseDate != null)
                {
                    row.DisburseDate = disburs.DisburseDate;
                }
                if (disburs.CollectionId != null && disburs.CollectionId != 0)
                {
                    row.CollectionId = disburs.CollectionId;
                }
                if (disburs.RepresentativeId != null && !(disburs.RepresentativeId.Equals("")))
                {
                    row.RepresentativeId = disburs.RepresentativeId;
                }
            }
                     
            return tda.Update(ds.Disbursement);
        }

        public int deleteDisbursement(int Id)
        {
            detailstda.DeleteByDisbursId(Id);
            return tda.DeleteByDisbursId(Id);
        }

        public Disbursement selectDisbursement(int Id)
        {
            tda.Fill(ds.Disbursement);
            SSISDataSet.DisbursementRow r = getRowById(Id);
            if (r != null)
            {
                disb = new Disbursement();
                disb.DisbursementId = r.DisbursementId;
                disb.DeptId = r.DeptId;
                disb.DisburseDate = r.DisburseDate;
                disb.Status = r.Status;
                disb.CollectionId = r.CollectionId;
                disb.RepresentativeId = r.RepresentativeId;

                //Detail
                disbDetails_list = selectDsibursementDetailsbyDisbursId(r.DisbursementId);
                disb.DisburDetails_list = disbDetails_list;

                return disb;
            }
            else
            {
                return null;
            }
        }

        //Savithri///////////////////////////////////////////

        public List<Disbursement> selectDisbursementbyDeptId(String deptId)
        {
            tda.Fill(ds.Disbursement);
            disburs_td = tda.GetDataByDeptId(deptId);
            if (disburs_td != null)
            {
                disb_list = new List<Disbursement>();

                foreach (SSISDataSet.DisbursementRow row in disburs_td.Rows)
                {

                    disb = new Disbursement();
                    disb.DisbursementId = disburs_td[0].DisbursementId;
                    disb.DeptId = disburs_td[0].DeptId;
                    disb.DisburseDate = disburs_td[0].DisburseDate;
                    disb.Status = disburs_td[0].Status;
                    disb.CollectionId = disburs_td[0].CollectionId;
                    disb.RepresentativeId = disburs_td[0].RepresentativeId;
                    disb_list.Add(disb);
                }

                return disb_list;
            }
            else
            {
                return null;
            }
        }


        public List<DisbursementCatalogue> getDisbursementListForDept(String deptId)
        {
            SSISDataSetTableAdapters.DisbursementListForDeptTableAdapter tda = new SSISDataSetTableAdapters.DisbursementListForDeptTableAdapter();
            int j = tda.Fill(ds.DisbursementListForDept, deptId);
            if (j > 0)
            {
                List<DisbursementCatalogue> list = new List<DisbursementCatalogue>();
                for (int i = 0; i < ds.DisbursementListForDept.Rows.Count; i++)
                {
                    DisbursementCatalogue dc = new DisbursementCatalogue();
                    dc.ItemId = ds.DisbursementListForDept[i].ItemId;
                    dc.CategoryName = ds.DisbursementListForDept[i].CategoryName;
                    dc.Catalogue_Name = ds.DisbursementListForDept[i].Description;
                    dc.RequestedQty = ds.DisbursementListForDept[i].RequestedQty;
                    dc.ReceivedQty = ds.DisbursementListForDept[i].ReceivedQty;

                    list.Add(dc);
                }

                return list;
            }
            else
            {
                return null;
            }

        }


        ///////////////Savithri//////////////////////////////////

        ///AMMT/////////
        public List<UnCollectedTime> getUnCollectedTime()
        {
            unCollectedtda.FillUnCollectedTimeByDept(ds.UnCollectedTime);
            List<UnCollectedTime> unCollectedTime_list = new List<UnCollectedTime>();
            if (ds.UnCollectedTime.Rows.Count > 0)
            {
                for (int i = 0; i < ds.UnCollectedTime.Rows.Count; i++)
                {
                    UnCollectedTime unt = new UnCollectedTime();
                    unt.UnCollectedNo = ds.UnCollectedTime[i].UnCollectedTime;
                    unt.DeptId = ds.UnCollectedTime[i].DeptId;

                    unCollectedTime_list.Add(unt);
                }

                return unCollectedTime_list;
            }
            else
            {
                return null;
            }
        }

        public List<DisbursementCatalogue> getDisbursementAndCatalogue(String deptId)
        {
            SSISDataSetTableAdapters.DisbursementCatalogueTableAdapter dc_tda = new SSISDataSetTableAdapters.DisbursementCatalogueTableAdapter();
            int j = dc_tda.Fill(ds.DisbursementCatalogue,deptId);
            if (j > 0)
            {
                List<DisbursementCatalogue> list = new List<DisbursementCatalogue>();
                foreach (SSISDataSet.DisbursementCatalogueRow row in ds.DisbursementCatalogue.Rows)
                {
                    DisbursementCatalogue dc = new DisbursementCatalogue();
                    dc.DeptId = row.DeptId;
                    dc.Catalogue_Name = row.Description;
                    dc.RequestedQty = row.RequestedQty;
                    dc.ReceivedQty = row.ReceivedQty;
                    dc.Status = row.Status;
                    list.Add(dc);
                }

                return list;
            }

            else
            {
                return null;
            }
        }

        public List<DisbursementCatalogue> getDisbursementAndCatalogueAndCollection()
        {
            dc_tda.Fill(ds.DisbursementAndCollection);
            List<DisbursementCatalogue> list = new List<DisbursementCatalogue>();
            for (int i = 0; i < ds.DisbursementAndCollection.Rows.Count; i++)
            {
                DisbursementCatalogue dc = new DisbursementCatalogue();
                dc.DisbursId = ds.DisbursementAndCollection[i].DisbursementId;
                dc.DeptId = ds.DisbursementAndCollection[i].DeptId;
                dc.DeptName = ds.DisbursementAndCollection[i].DeptName;
                dc.Place = ds.DisbursementAndCollection[i].Place;
                dc.Time = Convert.ToString(ds.DisbursementAndCollection[i].Time);
                dc.Status = ds.DisbursementAndCollection[i].Status;
                list.Add(dc);
            }
            return list;
        }


        public DisbursementCatalogue getDisbursementAndCatalogueAndCollectionByDeptId(String deptId)
        {
            dc_tda.Fill(ds.DisbursementAndCollection);
            SSISDataSet.DisbursementAndCollectionDataTable dt = dc_tda.GetDataByDeptId(deptId);
            if (dt.Rows.Count > 0)
            {
                SSISDataSet.DisbursementAndCollectionRow row = (SSISDataSet.DisbursementAndCollectionRow)dt.Rows[0];
                DisbursementCatalogue dc = new DisbursementCatalogue();
                dc.DisbursId = row.DisbursementId;
                dc.DeptId = row.DeptId;
                dc.DeptName = row.DeptName;
                dc.Place = row.Place;
                dc.Time = Convert.ToString(row.Time);
                dc.Status = row.Status;

                return dc;
            }
            else
            {
                return null;
            }
            
        }

        public List<Department> getDepartmentByDisbursementAssignedStatus()
        {
            SSISDataSetTableAdapters.DisbursementDepartmentTableAdapter tda = new SSISDataSetTableAdapters.DisbursementDepartmentTableAdapter();
            int j = tda.Fill(ds.DisbursementDepartment, "Assigned");
            if (j > 0)
            {
                List<Department> dept_list = new List<Department>();
                foreach (SSISDataSet.DisbursementDepartmentRow row in ds.DisbursementDepartment.Rows)
                {
                    Department dept = new Department();
                    dept.DeptId = row.DeptId;
                    dept.DeptName = row.DeptName;
                    dept_list.Add(dept);
                }
                return dept_list;
                }
                else
                {
                    return null;
                }
            }

        public Disbursement getDisbursementByDeptIdAndDate(String deptId, DateTime disburseDate)
        {
            tda.Fill(ds.Disbursement);
           disburs_td = tda.GetDataByDeptIdAndDate(deptId, disburseDate);
           if (disburs_td.Rows.Count > 0)
           {
               Disbursement d = new Disbursement();
               SSISDataSet.DisbursementRow row = (SSISDataSet.DisbursementRow)disburs_td.Rows[0];
               d.DisbursementId = row.DisbursementId;
               d.DeptId = row.DeptId;
               d.DisburseDate = row.DisburseDate;
               d.Status = row.Status;
               d.CollectionId = row.CollectionId;
               d.RepresentativeId = row.RepresentativeId;

               return d;
           }
           else
           {
               return null;
           }
        }

        public Disbursement getDisbursementByDeptIdAndAssignedStatus(String deptId)
        {
            tda.Fill(ds.Disbursement);
            disburs_td = tda.GetDataByDeptIdAndStatus(deptId, "Assigned");
            if (disburs_td.Rows.Count > 0)
            {
                disb_list = new List<Disbursement>();
                SSISDataSet.DisbursementRow row = (SSISDataSet.DisbursementRow)disburs_td.Rows[0];
                    Disbursement d = new Disbursement();
                    d.DisbursementId = row.DisbursementId;
                    d.DeptId = row.DeptId;
                    d.DisburseDate = row.DisburseDate;
                    d.Status = row.Status;
                    d.CollectionId = row.CollectionId;
                    d.RepresentativeId = row.RepresentativeId;
                    disb_list.Add(d);

                    return d;
                }
            else
            {
                return null;
            }
        }


        public List<DisbursementCatalogue> getDisbursedItemByItemIdAndAssignedStatus(String itemId)
        {
            SSISDataSetTableAdapters.DisbursedItemsTableAdapter tda = new SSISDataSetTableAdapters.DisbursedItemsTableAdapter();
            int j = tda.Fill(ds.DisbursedItems,itemId);
            if (j > 0)
            {
                List<DisbursementCatalogue> list = new List<DisbursementCatalogue>();
                foreach (SSISDataSet.DisbursedItemsRow row in ds.DisbursedItems.Rows)
                {
                    DisbursementCatalogue dc = new DisbursementCatalogue();
                    dc.DeptId = row.DeptId;
                    dc.Status = row.Status;
                    dc.ItemId = row.ItemId;
                    dc.RequestedQty = row.RequestedQty;
                    dc.ReceivedQty = row.ReceivedQty;
                    list.Add(dc);
                }

                return list;
            }
            else
            {
                return null;
            }
        }


        public ConsolidateRetrivalItem getDisbursementItemCount(String deptId, String itemId)
        {
            SSISDataSetTableAdapters.DisbursementItemCountTableAdapter tda = new SSISDataSetTableAdapters.DisbursementItemCountTableAdapter();
            int j = tda.Fill(ds.DisbursementItemCount, deptId, itemId);
            if (j > 0)
            {
                SSISDataSet.DisbursementItemCountRow row = (SSISDataSet.DisbursementItemCountRow)ds.DisbursementItemCount.Rows[0];
                ConsolidateRetrivalItem conri = new ConsolidateRetrivalItem();
                conri.DeptId = row.DeptId;
                conri.ItemCount = row.ItemCount;
                conri.ItemId = row.ItemId;
                
                return conri;
            }
            else
            {
                return null;
            }
        }

        public List<ConsolidateRetrivalItem> getRequisitionItemCount(String deptId)
        {
            SSISDataSetTableAdapters.RequisitionItemCountTableAdapter tda = new SSISDataSetTableAdapters.RequisitionItemCountTableAdapter();
            int j = tda.Fill(ds.RequisitionItemCount, deptId);
            if (j > 0)
            {
                List<ConsolidateRetrivalItem> list = new List<ConsolidateRetrivalItem>();
                foreach (SSISDataSet.RequisitionItemCountRow row in ds.RequisitionItemCount.Rows)
                {
                    ConsolidateRetrivalItem conri = new ConsolidateRetrivalItem();
                    conri.DeptId = row.DeptId;
                    conri.ItemCount = row.ItemCount;
                    conri.ItemId = row.ItemId;
                    list.Add(conri);
                }
                return list;
            }
            else
            {
                return null;
            }
        }

        /////////////////AMMT/////////////////////////////////////////

        ///////////////////////////////////////////////////////////////////////////

        //Disbursement Details
        public List<DisbursementDetails> selectAllDisbursementDetails()
        {
            detailstda.Fill(ds.DisbursementDetails);
            disbDetails_list = new List<DisbursementDetails>();
            for (int i = 0; i < ds.DisbursementDetails.Rows.Count; i++)
            {
                disbDetails = new DisbursementDetails();
                disbDetails.DisbursementId = ds.DisbursementDetails[i].DisbursementId;
                disbDetails.ItemId = ds.DisbursementDetails[i].ItemId;
                disbDetails.ReceivedQty = ds.DisbursementDetails[i].ReceivedQty;
                disbDetails.RequestedQty = ds.DisbursementDetails[i].RequestedQty;
                disbDetails_list.Add(disbDetails);
            }
            return disbDetails_list;
        }

        public int addDisbursementDetails(DisbursementDetails disbursDetails)
        {
            SSISDataSet.DisbursementDetailsRow newRow = ds.DisbursementDetails.NewDisbursementDetailsRow();
            newRow.DisbursementId = disbursDetails.DisbursementId;
            newRow.ItemId = disbursDetails.ItemId;
            newRow.ReceivedQty = disbursDetails.ReceivedQty;
            newRow.RequestedQty = disbursDetails.RequestedQty;
   
            ds.DisbursementDetails.Rows.Add(newRow);
            ds.EndInit();
            return detailstda.Update(ds.DisbursementDetails);
        }

        //private int getIndex(String disbursId,String itemId)
        //{
        //    SSISDataSet.DisbursementDetailsRow row = ds.DisbursementDetails.FindByDisbursementIdItemId(disbursId,itemId);            
        //    return ds.DisbursementDetails.Rows.IndexOf(row);
        //}

        private SSISDataSet.DisbursementDetailsRow getRowById(int disbursId, String itemId)
        {
            detailstda.Fill(ds.DisbursementDetails);
            return ds.DisbursementDetails.FindByDisbursementIdItemId(disbursId, itemId);
        }

        public int updateDisbursementDetails(DisbursementDetails disbursDetails, int disbursId, String itemId)
        {
            SSISDataSet.DisbursementDetailsRow row = getRowById(disbursId, itemId);
            if (row != null)
            {
                if (disbursDetails.ReceivedQty != null)
                {
                    row.ReceivedQty = disbursDetails.ReceivedQty;
                }
                if (disbursDetails.RequestedQty != null)
                {
                    row.RequestedQty = disbursDetails.RequestedQty;
                }
            }

            return detailstda.Update(ds.DisbursementDetails);
        }

        public int deleteDisbursementDetails(int disbursId, String itemId)
        {
            return detailstda.DeleteByDisbursIdAndItemId(disbursId, itemId);
        }

        public DisbursementDetails selectDisbursementDetails(int disbursId,String itemId)
        {
            detailstda.Fill(ds.DisbursementDetails);
            SSISDataSet.DisbursementDetailsRow r = getRowById(disbursId, itemId);
            if (r != null)
            {
                disbDetails = new DisbursementDetails();
                disbDetails.DisbursementId = r.DisbursementId;
                disbDetails.ItemId = r.ItemId;
                disbDetails.ReceivedQty = r.ReceivedQty;
                disbDetails.RequestedQty = r.RequestedQty;

                return disbDetails;
            }
            else
            {
                return null;
            }
        }

        //select by disbursement Id
        public List<DisbursementDetails> selectDsibursementDetailsbyDisbursId(int Id)
        {
            detailstda.Fill(ds.DisbursementDetails);
            disbursDetails_td = detailstda.GetDataByDisbursId(Id);
            disbDetails_list = new List<DisbursementDetails>();
            if (disbursDetails_td != null)
            {
                for (int i = 0; i < disbursDetails_td.Rows.Count; i++)
                {
                    disbDetails = new DisbursementDetails();
                    disbDetails.DisbursementId = disbursDetails_td[i].DisbursementId;
                    disbDetails.ItemId = disbursDetails_td[i].ItemId;
                    disbDetails.ReceivedQty = disbursDetails_td[i].ReceivedQty;
                    disbDetails.RequestedQty = disbursDetails_td[i].RequestedQty;
                    disbDetails_list.Add(disbDetails);
                }

                return disbDetails_list;
            }
            else
            {
                return null;
            }
        }

        
    }
}
